/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bettosimport.datos;

import com.bettosimport.entidades.Familia;
import com.bettosimport.entidades.Linea;
import com.bettosimport.entidades.Marca;
import com.bettosimport.entidades.Sucursal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Neil
 */
public class TablasMaestrasDAO {

    private static TablasMaestrasDAO _Instancia;

    private TablasMaestrasDAO() {
    }

    public static TablasMaestrasDAO Instancia() {
        if (_Instancia == null) {
            _Instancia = new TablasMaestrasDAO();
        }
        return _Instancia;
    }

    public ArrayList<Linea> listarLineas() throws Exception {
        ArrayList<Linea> lineas;
        Connection cn = null;
        try {
            lineas = new ArrayList<>();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_listarTM(?,?)}");
            cs.setString(1, "linea");
            cs.setInt(2, 0);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Linea l = new Linea();
                l.setId_linea(rs.getInt("id_linea"));
                l.setDesc_linea(rs.getString("desc_linea"));
                lineas.add(l);
            }
            return lineas;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public Linea buscarLinea(int id) throws Exception {
        Linea l;
        Connection cn = null;
        try {
            l = new Linea();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_buscarTM(?,?)}");
            cs.setString(1, "linea");
            cs.setInt(2, id);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                l.setId_linea(rs.getInt("id_linea"));
                l.setDesc_linea(rs.getString("desc_linea"));
            }
            return l;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public ArrayList<Familia> listarFamilias(int linea) throws Exception {
        ArrayList<Familia> familias;
        Connection cn = null;
        try {
            familias = new ArrayList<>();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_listarTM(?,?)}");
            cs.setString(1, "familia");
            cs.setInt(2, linea);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Familia f = new Familia();
                f.setId_fam(rs.getInt("id_fam"));
                f.setDesc_fam(rs.getString("desc_fam"));
                Linea l = new Linea();
                l.setDesc_linea(rs.getString("desc_linea"));
                f.setLinea(l);
                familias.add(f);
            }
            return familias;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public Familia buscarFamilia(int id) throws Exception {
        Familia f;
        Connection cn = null;
        try {
            f = new Familia();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_buscarTM(?,?)}");
            cs.setString(1, "familia");
            cs.setInt(2, id);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                f.setId_fam(rs.getInt("id_fam"));
                f.setDesc_fam(rs.getString("desc_fam"));
                Linea l = new Linea();
                l.setId_linea(rs.getInt("id_linea"));
                l.setDesc_linea(rs.getString("desc_linea"));
                f.setLinea(l);
            }
            return f;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public ArrayList<Marca> listarMarcas() throws Exception {
        ArrayList<Marca> marcas;
        Connection cn = null;
        try {
            marcas = new ArrayList<>();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_listarTM(?,?)}");
            cs.setString(1, "marca");
            cs.setInt(2, 0);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Marca m = new Marca();
                m.setId_marca(rs.getInt("id_marca"));
                m.setDesc_marca(rs.getString("desc_marca"));
                marcas.add(m);
            }
            return marcas;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public Marca buscarMarca(int id) throws Exception {
        Marca m;
        Connection cn = null;
        try {
            m = new Marca();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_buscarTM(?,?)}");
            cs.setString(1, "marca");
            cs.setInt(2, id);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                m.setId_marca(rs.getInt("id_marca"));
                m.setDesc_marca(rs.getString("desc_marca"));
            }
            return m;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public ArrayList<Sucursal> listarSucursales() throws Exception {
        ArrayList<Sucursal> sucursales;
        Connection cn = null;
        try {
            sucursales = new ArrayList<>();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_listarTM(?,?)}");
            cs.setString(1, "sucursal");
            cs.setInt(2, 0);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Sucursal m = new Sucursal();
                m.setId_suc(rs.getInt("id_suc"));
                m.setCiudad_suc(rs.getString("ciudad_suc"));
                m.setDir_suc(rs.getString("dir_suc"));
                sucursales.add(m);
            }
            return sucursales;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public Sucursal buscarSucursal(int id) throws Exception {
        Sucursal s;
        Connection cn = null;
        try {
            s = new Sucursal();
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_buscarTM(?,?)}");
            cs.setString(1, "sucursal");
            cs.setInt(2, id);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                s.setId_suc(rs.getInt("id_suc"));
                s.setCiudad_suc(rs.getString("ciudad_suc"));
                s.setDir_suc(rs.getString("dir_suc"));
            }
            return s;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }

    public int guardarmodificarTM(Object[] o) throws Exception {
        Connection cn = null;
        try {
            cn = ConexionDAO.Instancia().getConexion();
            CallableStatement cs = cn.prepareCall("{call sp_IULog_TM(?,?,?,?)}");
            cs.setString(1, o[0].toString());
            cs.setInt(2, Integer.parseInt(o[1].toString()));
            cs.setString(3, o[2].toString());
            cs.setInt(4, Integer.parseInt(o[3].toString()));
            cs.executeUpdate();
            return 1;
        } catch (Exception e) {
            throw e;
        } finally {
            cn.close();
        }
    }
}
